clear all
set more off
cd "/Users/yasenov/Dropbox (IPL)/RefugeeBan/data/demographics/"

capture log close
log using "../../logs/demographics", replace

***********
*********** 2010 A
***********

clear
qui do "nhgis0050_ds176_20105_2010_county.do"


rename jmae001 pop_total

gen pop_male_age_15_34 = jlze006 + jlze007 + jlze008 + jlze009 + jlze010 + jlze011 + jlze012

rename jmbe002 pop_white
rename jmbe003 pop_black
rename jmbe005 pop_asian

rename jmke003 pop_hisp

rename jmle007 pop_moved_in_1_yr_ago

rename joie001 hh_income

rename jq5e023 pop_police_male
rename jq5e059 pop_police_female

replace year = "2010"
keep year statea countya pop*

tempfile file2010
save `file2010', replace

***********
*********** 2011 A
***********

clear
qui do "nhgis0050_ds184_20115_2011_county.do"

rename mnte001 pop_total

gen pop_male_age_15_34 = mnie006 +  mnie007 +  mnie008 +  mnie009 +  mnie010 +  mnie011 +  mnie012

rename mnue002 pop_white
rename mnue003 pop_black
rename mnue005 pop_asian

rename mn3e003 pop_hisp

rename mn4e007 pop_moved_in_1_yr_ago

rename mp1e001 hh_income

rename mspe023 pop_police_male
rename mspe059 pop_police_female

rename msoe002 pop_in_lf
rename msoe005 pop_unempl
rename msoe007 pop_not_in_lf

replace year = "2011"
keep year statea countya pop*

tempfile file2011
save `file2011', replace

***********
*********** 2012 A
***********

clear
qui do "nhgis0050_ds191_20125_2012_county.do"

rename qspe001 pop_total

gen pop_male_age_15_34 = qsee006 +  qsee007 +  qsee008 +  qsee009 +  qsee010 +  qsee011 +  qsee012

rename qsqe002 pop_white
rename qsqe003 pop_black
rename qsqe005 pop_asian

rename qsze003 pop_hisp

rename qs0e007 pop_moved_in_1_yr_ago

rename qu1e001 hh_income

rename qxte023 pop_police_male
rename qxte059 pop_police_female

rename qxse002 pop_in_lf
rename qxse005 pop_unempl
rename qxse007 pop_not_in_lf

gen pop_hs_dropout = quse002 + quse003 + quse004 + quse005 + quse006 + quse007 + quse008 + quse009 + quse010 + quse011 + quse012 + quse013 + quse014 + quse015 + quse016 
gen pop_hs_grad = quse017 + quse018
gen pop_college = quse022 + quse023 + quse024 + quse025

replace year = "2012"
keep year statea countya pop*

tempfile file2012
save `file2012', replace

***********
*********** 2013 A
***********

clear
qui do "nhgis0050_ds201_20135_2013_county.do"
rename uepe001 pop_total

gen pop_male_age_15_34 = ueee006 +  ueee007 +  ueee008 +  ueee009 +  ueee010 +  ueee011 +  ueee012

rename ueqe002 pop_white
rename ueqe003 pop_black
rename ueqe005 pop_asian

rename ueze003 pop_hisp

rename ue0e007 pop_moved_in_1_yr_ago

rename uhde001 hh_income

rename uj9e023 pop_police_male
rename uj9e059 pop_police_female

rename uj8e002 pop_in_lf
rename uj8e005 pop_unempl
rename uj8e007 pop_not_in_lf

gen pop_hs_dropout = ugse002 + ugse003 + ugse004 + ugse005 + ugse006 + ugse007 + ugse008 + ugse009 + ugse010 + ugse011 + ugse012 + ugse013 + ugse014 + ugse015 + ugse016 
gen pop_hs_grad = ugse017 + ugse018
gen pop_college = ugse022 + ugse023 + ugse024 + ugse025

replace year = "2013"
keep year statea countya pop*

tempfile file2013
save `file2013', replace

***********
*********** 2014 A
***********

clear
qui do "nhgis0050_ds206_20145_2014_county.do"
rename aba1e001 pop_total

gen pop_male_age_15_34 = abaqe006 +  abaqe007 +  abaqe008 +  abaqe009 +  abaqe010 +  abaqe011 +  abaqe012

rename aba2e002 pop_white
rename aba2e003 pop_black
rename aba2e005 pop_asian

rename abbbe003 pop_hisp

rename abbce007 pop_moved_in_1_yr_ago

rename abdpe001 hh_income

rename abgie023 pop_police_male
rename abgie059 pop_police_female

rename abgfe002 pop_in_lf
rename abgfe005 pop_unempl
rename abgfe007 pop_not_in_lf

gen pop_hs_dropout = abc4e002 + abc4e003 + abc4e004 + abc4e005 + abc4e006 + abc4e007 + abc4e008 + abc4e009 + abc4e010 + abc4e011 + abc4e012 + abc4e013 + abc4e014 + abc4e015 + abc4e016 
gen pop_hs_grad = abc4e017 + abc4e018
gen pop_college = abc4e022 + abc4e023 + abc4e024 + abc4e025

replace year = "2014"
keep year statea countya pop*

tempfile file2014
save `file2014', replace

***********
*********** 2015 A
***********

clear
qui do "nhgis0050_ds215_20155_2015_county.do"
rename adkwe001 pop_total

gen pop_male_age_15_34 = adkle006 +  adkle007 +  adkle008 +  adkle009 +  adkle010 +  adkle011 +  adkle012

rename adkxe002 pop_white
rename adkxe003 pop_black
rename adkxe005 pop_asian

rename adk6e003 pop_hisp

rename adk7e007 pop_moved_in_1_yr_ago

rename adnke001 hh_income

rename adple023 pop_police_male
rename adple059 pop_police_female

rename adpie002 pop_in_lf
rename adpie005 pop_unempl
rename adpie007 pop_not_in_lf

gen pop_hs_dropout = admze002 + admze003 + admze004 + admze005 + admze006 + admze007 + admze008 + admze009 + admze010 + admze011 + admze012 + admze013 + admze014 + admze015 + admze016 
gen pop_hs_grad = admze017 + admze018
gen pop_college = admze022 + admze023 + admze024 + admze025

replace year = "2015"
keep year statea countya pop*

tempfile file2015
save `file2015', replace


***********
*********** 2016 A
***********

clear
qui do "nhgis0050_ds225_20165_2016_county.do"
rename af2le001 pop_total

gen pop_male_age_15_34 = af2ae006 +  af2ae007 +  af2ae008 +  af2ae009 +  af2ae010 +  af2ae011 +  af2ae012

rename af2me002 pop_white
rename af2me003 pop_black
rename af2me005 pop_asian

rename af2ve003 pop_hisp

rename af2we007 pop_moved_in_1_yr_ago

rename af49e001 hh_income

rename af7ae023 pop_police_male
rename af7ae059 pop_police_female

rename af67e002 pop_in_lf
rename af67e005 pop_unempl
rename af67e007 pop_not_in_lf

gen pop_hs_dropout = af4oe002 + af4oe003 + af4oe004 + af4oe005 + af4oe006 + af4oe007 + af4oe008 + af4oe009 + af4oe010 + af4oe011 + af4oe012 + af4oe013 + af4oe014 + af4oe015 + af4oe016 
gen pop_hs_grad = af4oe017 + af4oe018
gen pop_college = af4oe022 + af4oe023 + af4oe024 + af4oe025

replace year = "2016"
keep year statea countya pop*

tempfile file2016
save `file2016', replace

***********
*********** 2017 A
***********

clear
qui do "nhgis0050_ds233_20175_2017_county.do"
rename ahy1e001 pop_total

gen pop_male_age_15_34 = ahyqe006 +  ahyqe007 +  ahyqe008 +  ahyqe009 +  ahyqe010 +  ahyqe011 +  ahyqe012

rename ahy2e002 pop_white
rename ahy2e003 pop_black
rename ahy2e005 pop_asian

rename ahzbe003 pop_hisp

rename ahzce007 pop_moved_in_1_yr_ago

rename ah1pe001 hh_income

rename ah3se023 pop_police_male
rename ah3se059 pop_police_female

rename ah3pe002 pop_in_lf
rename ah3pe005 pop_unempl
rename ah3pe007 pop_not_in_lf

gen pop_hs_dropout = ah04e002 + ah04e003 + ah04e004 + ah04e005 + ah04e006 + ah04e007 + ah04e008 + ah04e009 + ah04e010 + ah04e011 + ah04e012 + ah04e013 + ah04e014 + ah04e015 + ah04e016 
gen pop_hs_grad = ah04e017 + ah04e018
gen pop_college = ah04e022 + ah04e023 + ah04e024 + ah04e025

replace year = "2017"
keep year statea countya pop*

tempfile file2017
save `file2017', replace

***********
*********** 2018 A
***********

clear
qui do "nhgis0050_ds239_20185_2018_county.do"
rename ajwme001 pop_total

gen pop_male_age_15_34 = ajwbe006 +  ajwbe007 +  ajwbe008 +  ajwbe009 +  ajwbe010 +  ajwbe011 +  ajwbe012

rename ajwne002 pop_white
rename ajwne003 pop_black
rename ajwne005 pop_asian

rename ajwwe003 pop_hisp

rename ajwxe007 pop_moved_in_1_yr_ago

rename ajzae001 hh_income

rename aj1fe023 pop_police_male
rename aj1fe059 pop_police_female

rename aj1ce002 pop_in_lf
rename aj1ce005 pop_unempl
rename aj1ce007 pop_not_in_lf

gen pop_hs_dropout = ajype002 + ajype003 + ajype004 + ajype005 + ajype006 + ajype007 + ajype008 + ajype009 + ajype010 + ajype011 + ajype012 + ajype013 + ajype014 + ajype015 + ajype016 
gen pop_hs_grad = ajype017 + ajype018
gen pop_college = ajype022 + ajype023 + ajype024 + ajype025

replace year = "2018"
keep year statea countya pop*

tempfile file2018
save `file2018', replace

***********
*********** 2018 B
***********

clear
qui do "nhgis0050_ds240_20185_2018_county.do"

rename aj7ve002 pop_native
rename aj7ve003 pop_fb

replace year = "2018"
keep year statea countya pop*

merge 1:1 statea countya using `file2018', nogen
save `file2018', replace

***********
*********** 2017 B
***********

clear
qui do "nhgis0050_ds234_20175_2017_county.do"

rename ah9ie002 pop_native
rename ah9ie003 pop_fb

replace year = "2017"
keep year statea countya pop*

merge 1:1 statea countya using `file2017', nogen
save `file2017', replace

***********
*********** 2016 B
***********

clear
qui do "nhgis0050_ds226_20165_2016_county.do"

rename agc0e002 pop_native
rename agc0e003 pop_fb

replace year = "2016"
keep year statea countya pop*

merge 1:1 statea countya using `file2016', nogen
save `file2016', replace

***********
*********** 2015 B
***********

clear
qui do "nhgis0050_ds216_20155_2015_county.do"

rename advbe002 pop_native
rename advbe003 pop_fb

replace year = "2015"
keep year statea countya pop*

merge 1:1 statea countya using `file2015', nogen
save `file2015', replace

***********
*********** 2014 B
***********

clear
qui do "nhgis0050_ds207_20145_2014_county.do"

rename abl5e002 pop_native
rename abl5e003 pop_fb

replace year = "2014"
keep year statea countya pop*

merge 1:1 statea countya using `file2014', nogen
save `file2014', replace

***********
*********** 2013 B
***********

clear
qui do "nhgis0050_ds202_20135_2013_county.do"

rename up2e002 pop_native
rename up2e003 pop_fb

replace year = "2013"
keep year statea countya pop*

merge 1:1 statea countya using `file2013', nogen
save `file2013', replace

***********
*********** 2012 B
***********

clear
qui do "nhgis0050_ds192_20125_2012_county.do"

rename q3je002 pop_native
rename q3je003 pop_fb

replace year = "2012"
keep year statea countya pop*

merge 1:1 statea countya using `file2012', nogen
save `file2012', replace

***********
*********** 2011 B
***********

clear
qui do "nhgis0050_ds185_20115_2011_county.do"

rename myee002 pop_native
rename myee003 pop_fb

replace year = "2011"
keep year statea countya pop*

merge 1:1 statea countya using `file2011', nogen
save `file2011', replace


***********
*********** 2010 B
***********

clear
qui do "nhgis0050_ds177_20105_2010_county.do"

rename jwue002 pop_native
rename jwue003 pop_fb

replace year = "2010"
keep year statea countya pop*

merge 1:1 statea countya using `file2010', nogen
save `file2010', replace


***********
*********** 2011 C
***********

clear
qui do "nhgis0050_ds188_20113_2011_county.do"

gen pop_hs_dropout = nt7e002 + nt7e003 + nt7e004 + nt7e005 + nt7e006 + nt7e007 + nt7e008 + nt7e009 + nt7e010 + nt7e011 + nt7e012 + nt7e013 + nt7e014 + nt7e015 + nt7e016 
gen pop_hs_grad = nt7e017 + nt7e018
gen pop_college = nt7e022 + nt7e023 + nt7e024 + nt7e025

replace year = "2011"
keep year statea countya pop*

merge 1:1 statea countya using `file2011', nogen
save `file2011', replace


***********
*********** 2010 C
***********

clear
qui do "nhgis0050_ds180_20103_2010_county.do"

gen pop_hs_dropout = ksle002 + ksle003 + ksle004 + ksle005 + ksle006 + ksle007 + ksle008 + ksle009 + ksle010 + ksle011 + ksle012 + ksle013 + ksle014 + ksle015 + ksle016 
gen pop_hs_grad = ksle017 + ksle018
gen pop_college = ksle022 + ksle023 + ksle024 + ksle025

replace year = "2010"
keep year statea countya pop*

merge 1:1 statea countya using `file2010', nogen
save `file2010', replace

***********
***********
***********

clear
append using `file2010'
append using `file2011'
append using `file2012'
append using `file2013'
append using `file2014'
append using `file2015'
append using `file2016'
append using `file2017'
append using `file2018'

destring year, replace

sum
bysort year: sum

tab year, m

***********
***********
***********

gen s_white = pop_white / pop_total
gen s_black = pop_black / pop_total
gen s_asian = pop_asian / pop_total

gen s_hisp = pop_hisp / pop_total

gen s_hs_dropout = pop_hs_dropout / pop_total
gen s_hs_grad = pop_hs_grad / pop_total
gen s_college = pop_college / pop_total

gen s_native = pop_native / pop_total
gen s_fb = pop_fb / pop_total

gen s_unempl = pop_unempl / pop_in_lf
gen s_not_in_lf = pop_not_in_lf / pop_total

gen s_police_male = pop_police_male / (pop_police_male + pop_police_female)

gen s_male_age_15_34 = pop_male_age_15_34 / pop_total

gen s_moved_in_1_yr_ago = pop_moved_in / pop_total

***********
***********
***********

rename (statea countya) (fips_state fips_county)
gen fips = fips_state + fips_county

order fips* year pop* s_*

drop if fips_state == "72"
tab year, m

* ADD POPULATION DENSITY
merge m:1 fips_stat fips_c using  "../CityMapping/fips_area.dta", keep(1 3)
unique fips if _merge == 1
drop _merge

gen pop_density = pop_total / landarea
drop landarea 

sum
desc
compress
isid fips year
 
save "demographics.dta", replace

capture log close
